/**
 * 文件名称:  CarZhServiceImpl.java
 * 文件序号:  序号
 * 内容描述:  文件简单描述，多行换行书写
 * 版权声明:  山东鸿蝶信息技术有限公司
 * 创建用户:  zhangkai
 * 创建日期:  2017/3/1 14:50
 * 修改用户:  无
 * 修改日期:  无
 * 修改说明:  无
 * 文件版本:  V1.0
 * 文件路径:
 */

package com.hongdee.crm.base.service.impl;


import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.base.dao.ICarDao;
import com.hongdee.crm.base.service.ICarZhService;
import com.hongdee.crm.carConfig.service.ITcrmCarConfService;
import com.hongdee.crm.task.service.ITCrmTimeTaskService;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.*;

/**
 * 名  称:CarZhServiceImpl
 * 类  型: class
 * 描  述: 用一句话描述类的作用
 * 路  径:com.hongdee.crm.base.service.impl
 * 作  者:zhangkai
 * 日  期:2017/3/1 14:50
 */
@Service
public class CarZhServiceImpl implements ICarZhService {
    private Logger logger = LoggerFactory.getLogger(CarZhServiceImpl.class);
    @Autowired
    private ICarDao carDao;

    @Autowired
    private ITCrmTimeTaskService itCrmTaskService;

    @Autowired
    private ITcrmCarConfService confService;

    private enum FROM_COMPANY_TYPE {
        NO, YES
    }

    /**
     * @param start
     * @param limit
     * @param params
     * @param uid
     * @param data
     * @return
     * @throws Exception
     */
    public Map page(String start, String limit, Map params, String uid, List<Map> data) throws Exception {
        return this.getZhQueryPage(start, limit, params, uid, data);
    }

    /**
     * 分页组合查询
     *
     * @param start
     * @param limit
     * @param uid
     * @param paramsMap
     * @return
     * @throws Exception
     */
    @Override
    public Map page(String start, String limit, Map paramsMap, String uid) throws Exception {
        return this.getZhQueryPage(start, limit, paramsMap, uid, null);
    }

    private Map getZhQueryPage(String start, String limit, Map paramsMap, String uid, List<Map> data) throws Exception {
        Map temp = new HashMap();
        temp.put("total", 0);
        temp.put("dataList", new ArrayList());
        //UID
        //根据用户取公司 ID，默认仅能查询本公司的车辆信息
        String companyId = StringUtil.formatEmpty(paramsMap.get("companyId"));
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        String oArea = StringUtil.formatEmpty(paramsMap.get("oArea"));
        String brand = StringUtil.formatEmpty(paramsMap.get("brand"));
        Long curTime = new Date().getTime();
        //组合查询
        StringBuffer temp2Buf = new StringBuffer();

        StringBuffer carBuf = new StringBuffer();
        List params = new ArrayList();
        carBuf.append("SELECT * FROM (SELECT tc.plate_num, car_id, ");
        carBuf.append("SUM(IFNULL(real_amount, 0)) ljcz, ");
        carBuf.append("count( DISTINCT FROM_UNIXTIME(balance_date / 1000, '%Y%m%d') ) AS jccs,  ");
        carBuf.append("round( sum(IFNULL(real_amount, 0)) / count( DISTINCT FROM_UNIXTIME(balance_date / 1000, '%y%m%d') ),2) avgCz, ");
        carBuf.append("SUM(CASE WHEN `balance_date` BETWEEN ? AND ? THEN real_amount ELSE 0 END) AS mCz,  ");
        Calendar cl = Calendar.getInstance();
        cl.setTime(new Date());
        cl.add(Calendar.MONTH, -1);
        params.add(cl.getTime().getTime());
        params.add(curTime);
        carBuf.append("tc.first_enter_date as firsEnterDate, ");
        carBuf.append("MAX( CASE WHEN real_amount > '0.00' THEN balance_date ELSE 0 END ) AS lastEnterDate,  ");
        carBuf.append("tc.vin,  ");
        carBuf.append("tc.from_company,  ");
        carBuf.append("tc.owner_city, ");
        carBuf.append("tc.owner_name,  ");
        carBuf.append("tc.series, ");
        carBuf.append("tc.car_age, ");
        carBuf.append("tc.run_mile,tc.contact_phone,tc.active,cd.item,cd.value,cd.card_type  ");
        carBuf.append(" from t_crm_car tc,");
//        carBuf.append(" from (select car.*,bc.o_name from t_crm_car car,");
////        carBuf.append(" (select * from t_bc_organ  where 1=1  ");
////        Constant.companyAndAreaAndBrand(uid,companyId,oArea,brand,carBuf,params,"");
////        carBuf.append(" ) bc ");
//        carBuf.append(" where car.company_id=bc.id )tc ,");
        carBuf.append(" t_crm_car_workorder t1,t_crm_card cd ");
        carBuf.append(" WHERE t1.vin = tc.vin and tc.company_id=t1.company_id AND t1.real_amount > '0.00'   ");
        carBuf.append(" and tc.company_id = ? ");
        params.add(companyId);

        if(!paramsMap.get("cardType").equals("")
                || !paramsMap.get("cardValueStart").equals("")
                || !paramsMap.get("cardValueEnd").equals("")
                || !paramsMap.get("cardItem").equals("")){

            carBuf.append(" AND cd.tel_num = tc.owner_tel ");

            if(!paramsMap.get("cardType").equals("")){
                carBuf.append(" AND cd.card_type = ? ");
                params.add(paramsMap.get("cardType"));
            }
            if(!paramsMap.get("cardValueStart").equals("")){
                carBuf.append(" AND cd.value >= ? ");
                params.add(paramsMap.get("cardValueStart"));
            }
            if(!paramsMap.get("cardValueEnd").equals("")){
                carBuf.append(" AND cd.value <= ? ");
                params.add(paramsMap.get("cardValueEnd"));
            }
            if(!paramsMap.get("cardItem").equals("")){
                carBuf.append(" AND cd.item LIKE ? ");
                params.add("%"+paramsMap.get("cardItem")+"%");
            }
        }

        List ownerOldList = (ArrayList)(paramsMap.get("ownerOld"));
        Map KVMap = new HashMap();
        KVMap.put("1"," AND  tc.owner_age BETWEEN 18 AND 25 ");
        KVMap.put("2"," AND tc.owner_age BETWEEN 26 AND 30 ");
        KVMap.put("3"," AND tc.owner_age BETWEEN 31 AND 35 ");
        KVMap.put("4"," AND tc.owner_age BETWEEN 36 AND 40 ");
        /*KVMap.put("1"," AND  tc.owner_age BETWEEN '18' AND '25' ");
        KVMap.put("2"," AND tc.owner_age BETWEEN '26' AND '30' ");
        KVMap.put("3"," AND tc.owner_age BETWEEN '31' AND '35' ");
        KVMap.put("4"," AND tc.owner_age BETWEEN '36' AND '40' ");*/
        KVMap.put("5"," AND tc.owner_age > 40 ");
        if(ownerOldList.size() > 0){
            for(int i = 0;i < ownerOldList.size(); i++){
                carBuf.append(KVMap.get(ownerOldList.get(i)));
            }
        }

        if(!paramsMap.get("ownerGender").equals("")){
            carBuf.append(" AND tc.owner_gender = ? ");
            params.add(paramsMap.get("ownerGender"));
        }

        if(!paramsMap.get("group").equals("")){
            carBuf.append(" AND t1.type_group = (SELECT label FROM t_bc_newdict WHERE code = ? ) ");
            params.add(paramsMap.get("group"));
        }
        if(!paramsMap.get("type").equals("")){
            carBuf.append(" AND t1.type = (SELECT label FROM t_bc_newdict WHERE code = ? ) ");
            params.add(paramsMap.get("type"));
        }
        if(!paramsMap.get("maintainType").equals("")){
            carBuf.append(" AND t1.maintain_type = (SELECT label FROM t_bc_newdict WHERE code = ? ) ");
            params.add(paramsMap.get("maintainType"));
        }

        /* 新规则，按车驾号分配车辆 */
//        Constant.VIN_TO_SQL(carBuf, uid, "tc");
        if (companyId.equals(TokenUtil.getCompanyId(uid))) {
            confService.crmCarConfToSql(uid, carBuf, "tc", companyId);
        }
        Map res = null;


        //判断是否本店购车
        Object fromCompany = paramsMap.get("fromCompany");
        if (null != fromCompany && !fromCompany.toString().equals("")) {
            //fromcompany 1 查询是本店购车
            //            0 查询不是本店购车
            if (fromCompany.equals(String.valueOf(FROM_COMPANY_TYPE.YES.ordinal()))) {
                carBuf.append(" and tc.from_company = '是' ");
            } else {
                carBuf.append(" and tc.from_company <> '是' ");
            }
        }
//        // 查询其分配的车辆
//        Map info = refCar(uid);
//        //未分配车辆
//        if (null == info || info.size() == 0) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//
//        //车辆分配类型
//        String refType = String.valueOf(info.get("type"));
//        if (StringUtils.isEmpty(refType) || "null".equals(refType.toLowerCase())) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//        //车辆分配值，车属号 或车尾数，vin时 另行处理
//        String refValue = String.valueOf(info.get("value"));
//        refValue = refValue.replaceAll(",", "");
//        //根据类型取车辆
//
//        switch (refType) {
//            case Constant.CAR_WH:
//                carBuf.append(" AND  tc.plate_num REGEXP ? ");
//                params.add("[" + refValue + "]$");
//                break;
//            case Constant.CAR_WS:
//                //先不写
//                carBuf.append(" AND tc.plate_num REGEXP ? ");
//                params.add(".*[" + refValue + "][A-Za-z]*$");
//                //根据尾数查询
//                break;
//            case Constant.CAR_ALL:
//                //全部车辆
//                break;
//            default:
//                throw new Exception("车辆分配错误，请联系管理员！");
//        }
        //车系参数
        Object seriesObj = paramsMap.get("series");
        if (null != seriesObj) {
            List seriesLs = (List) seriesObj;
            if (seriesLs.size() > 0) {
//                carBuf.append(" AND tc.series in ( ");
//                for (Object obj : seriesLs) {
//                    carBuf.append(" ? ,");
//                    params.add(obj);
//                }
//                carBuf.append(" '' )");
                StringBuffer tempBuf = new StringBuffer();
                carBuf.append(" and ( ");
                for (Object str : seriesLs) {
                    tempBuf.append(" locate(? ,tc.series) OR");
                    params.add((String) str);
                }
                carBuf.append(tempBuf.substring(0, tempBuf.length() - 2).toString());
                carBuf.append(" ) ");
            }
        }
        //车龄参数
        //车龄参数
        Object ageobj = paramsMap.get("age");
        if (null != ageobj) {
            List ageLs = (List) ageobj;
            if (ageLs.size() > 0) {
                carBuf.append(" AND(  ");
                StringBuffer tempAge = new StringBuffer(" tc.car_age IN ( ");
                StringBuffer temAgeG = new StringBuffer(" tc.car_age >= ? ");
                Object lsYear = null;
                for (Object obj : ageLs) {
                    if (Integer.valueOf(String.valueOf(obj)) > 8) {
                        lsYear = obj;
                    } else {
                        tempAge.append("?,");
                        params.add(Integer.valueOf((String) obj));
                    }
                }
                tempAge.append(" '')  ");

                if (null != lsYear) {
                    tempAge.append(" OR (").append(temAgeG).append(") ");
                    params.add(Integer.valueOf((String) lsYear));
                }
                carBuf.append(tempAge);
                carBuf.append(" )");
            }
        }
        //里程
        Object lcObj = paramsMap.get("lc");
        if (null != lcObj) {
            List<String> lcLs = (List<String>) lcObj;
            if (lcLs.size() > 0) {

                StringBuffer lsBuf = new StringBuffer();
                for (String lcTemp : lcLs) {
                    if (lcTemp.indexOf("-") > 0) {
                        String[] lcArray = lcTemp.split("-");
                        lsBuf.append(" OR (tc.run_mile BETWEEN ? AND ? ) ");
                        params.add(Integer.valueOf(lcArray[0]));
                        params.add(Integer.valueOf(lcArray[1]));
                    }
                }
                if (lsBuf.toString().length() > 0) {
                    carBuf.append(" AND (1 >2 ");
                    carBuf.append(lsBuf.toString());
                    carBuf.append(" )");
                }

            }
        }
        //行政区域
        if (paramsMap.get("area") != null) {
            Object areaObj = paramsMap.get("area");
            String areaStr = areaObj.toString().replace(",", "|").replace("[", "").replace(" ", "").replace("]", "").replace("\"", "");
            if (areaStr.length() > 0) {
                /**
                 * 区域查询由城市改为详细地址
                 carBuf.append(" AND tc.owner_city regexp ?");
                 **/
                carBuf.append(" AND (tc.owner_address regexp ? or  tc.owner_city regexp ? )");

                params.add(areaStr);
                params.add(areaStr);
            }
        }

        //车尾号
        Object whObj = paramsMap.get("wh");
        if (null != whObj) {
            List<String> whList = (List<String>) whObj;
            if (whList.size() > 0) {
                String whTemp = "";
                for (String tm : whList) {
                    whTemp += tm;
                }
                carBuf.append(" AND  tc.plate_num REGEXP ? ");
                params.add("[" + whTemp + "]$");
            }
        }
        //车属数
        Object wsObj = paramsMap.get("ws");
        if (null != wsObj) {
            List<String> wsList = (List<String>) wsObj;
            if (wsList.size() > 0) {
                String whTemp = "";
                for (String tm : wsList) {
                    whTemp += tm;
                }
                carBuf.append(" AND tc.plate_num REGEXP ? ");
                params.add(".*[" + whTemp + "][A-Za-z]*$");
            }
        }


        //车架号
        String vin = String.valueOf(paramsMap.get("vin"));
        if (!StringUtils.isEmpty(vin) && !"null".equals(vin.toLowerCase()))

        {
            carBuf.append("AND tc.vin like ? ");
            params.add("%" + vin + "%");
        }

        //市场活动标签
        String tag = String.valueOf(paramsMap.get("marketTag"));
        if (!StringUtils.isEmpty(tag) && !"null".equals(tag.toLowerCase()))

        {
            carBuf.append("AND tc.market_tag = ? ");
            params.add(tag);
        }
        /**
         * 新增维系人、销售顾问过滤
         * 2017、09、18
         * Liu XiaoLong
         */
        //维系人
        String wxId = String.valueOf(paramsMap.get("wxId"));
        if (!StringUtils.isEmpty(wxId))

        {
            carBuf.append("AND tc.wx_id like ? ");
            params.add("%" + wxId + "%");
        }
        //销售顾问
        String saler = String.valueOf(paramsMap.get("saler"));
        if (!StringUtils.isEmpty(saler))

        {
            carBuf.append("AND tc.saler like ? ");
            params.add("%" + saler + "%");
        }


        // 工单阶段
        Object balanceObj = paramsMap.get("balanceDate");
        if (null != balanceObj) {
            Map balMap = (Map) balanceObj;
            if (balMap.size() > 0) {
                String startT = String.valueOf(balMap.get("start"));
                String endT = String.valueOf(balMap.get("end"));
                Long sTL = -1L;
                Long eTL = -1L;
                if (!"null".equals(startT) && startT != null && !"".equals(startT)) {
                    sTL = DateUtils.parseTime(startT + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
                }
                if (!"null".equals(endT) && endT != null && !"".equals(endT)) {
                    eTL = DateUtils.parseTime(endT + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
                }
                // 拼接 SQL
                if (sTL > 0 || eTL > 0) {
                    carBuf.append(" AND ( 1 = 1 ");
                    if (sTL > 0) {
                        carBuf.append(" AND t1.balance_date >= ? ");
                        params.add(sTL);
                    }
                    if (eTL > 0) {
                        carBuf.append(" AND t1.balance_date <= ? ");
                        params.add(eTL);
                    }
                    carBuf.append(" )");
                }
            }
        }
        //工单类别
        Object workTypeObj = paramsMap.get("workType");
        if (workTypeObj != null) {
            String workType = (String) workTypeObj;
            if (workType.length() > 0) {
                carBuf.append(" AND t1.type = ? ");
                params.add(workType);
            }
        }
        // 维修类别
        Object wxType = paramsMap.get("wxType");
        if (null != wxType) {
            List wxLs = (List) wxType;
            if (wxLs.size() > 0) {
                carBuf.append(" AND t1.wx_type in ( ");
                for (Object typeTemp : wxLs) {
                    carBuf.append("?,");
                    if (typeTemp.equals("维修业务")) {
                        typeTemp = "维修";
                    }
                    params.add(typeTemp);
                }
                carBuf.append(" '' )");

            }
        }
        //质保状态
        String zbStatus = String.valueOf(paramsMap.get("zbStatus"));
        if (!"null".equals(zbStatus) && zbStatus.length() > 0) {
            carBuf.append(" AND EXISTS (SELECT 1 from t_crm_car_exsurance  ex2 where ex2.user_carid  = tc.id ");

            switch (zbStatus) {
                case "0":
                    //在质保-里程 和进间都没进入
                    carBuf.append("  and ex2.mile_status = 0 and t1.balance_date < ex2.restart_insurance ");
                    break;
                case "1":
                    // 在延保 ：1 里程没进入时间进入，里程时入时间没进入，时间里程都进入
                    carBuf.append(" and ex2.mile_status < 2 and ( t1.balance_date  BETWEEN  ex2.restart_insurance and ex2.restop_insurance ) ");
                    break;
                case "2":
                    //里程超出 或时间超出
                    carBuf.append(" and (ex2.mile_status = 2 or t1.balance_date  > ex2.restop_insurance ) ");
                    break;
                default:
                    break;
            }
            //延保类别
            Object ybObj = paramsMap.get("ybType");
            if (null != ybObj) {
                List<String> ybLs = (List) ybObj;
                if (ybLs.size() > 0) {
                    carBuf.append(" AND ex2.exsurance_company_id IN( ");
                    for (String tempYb : ybLs) {
                        carBuf.append("?,");
                        params.add(tempYb);
                    }
                    carBuf.append(" '' ) ");
                    // carBuf.append(" )");
                }
            }
            carBuf.append(" )");
        }
        //保险类别
        /***
         * 暂时这样
         * ****/
        Object bxObj = paramsMap.get("bx");
        if (null != bxObj) {
            List<String> bxList = (List<String>) bxObj;
            if (bxList.size() > 0) {
                carBuf.append("AND EXISTS( ");
                carBuf.append(" SELECT 1 FROM t_crm_car_insurance ix WHERE ix.user_carid = tc.id  and  (t1.balance_date  BETWEEN ix.contractstartinsurance  and ix.contractstopinsurance) ");
                StringBuffer bxBuf = new StringBuffer();
                for (String bxTemp : bxList) {
                    switch (bxTemp) {
                        case "0":
                            bxBuf.append(" OR ix.type = '1' ");
                            break;
                        case "1":
                            bxBuf.append(" OR ix.type in ('2','3','4','5' )");
                            break;
                        default:
                            break;
                    }
                }
                if (bxBuf.toString().length() > 0) {
                    carBuf.append("AND ( 1 > 2 ");
                    carBuf.append(bxBuf.toString());
                    carBuf.append(" )");
                }
                carBuf.append(" )");
            }
        }
        //保险项目
        Object bxItemHhObj = paramsMap.get("bxItem");
        if (null != bxItemHhObj) {
            List<String> bxItemList = (List<String>) bxItemHhObj;
            if (bxItemList.size() > 0) {
                carBuf.append("AND EXISTS( ");
                carBuf.append(" SELECT 1 FROM t_crm_car_insurance ix WHERE ix.user_carid = tc.id  and  (t1.balance_date  BETWEEN ix.contractstartinsurance  and ix.contractstopinsurance)  ");
                StringBuffer bxBuf = new StringBuffer();
                for (String bxTemp : bxItemList) {
                    switch (bxTemp) {
                        case "0":
                            carBuf.append(" AND ix.boli = '1' ");
                            break;
                        case "1":
                            carBuf.append(" AND ix.huahen ='1' ");
                            break;
                    }
                }
                if (bxBuf.toString().length() > 0) {
                    carBuf.append("AND ( 1 =1 ");
                    carBuf.append(bxBuf.toString());
                    carBuf.append(" )");
                }

                carBuf.append(" ) ");
            }
        }
        carBuf.append("GROUP BY car_id order by tc.car_age*1 asc ) AS TEMP WHERE 1=1 ");
        if (null != balanceObj) {
            Map balMap = (Map) balanceObj;
            if (balMap.size() > 0) {
                String startT = String.valueOf(balMap.get("start"));
                String endT = String.valueOf(balMap.get("end"));
                if (!"".equals(startT) && !"".equals(endT)) {
                    // 工单累计产值
                    Object ljCzObj = paramsMap.get("ljCz");
                    if (null != ljCzObj) {
                        List<String> ljCzLs = (List<String>) ljCzObj;
                        if (ljCzLs.size() > 0) {
                            StringBuffer lsBuf = new StringBuffer();
                            String strTemp = " and ( ";
                            for (String lcTemp : ljCzLs) {
                                if (lcTemp.indexOf("-") > 0) {
                                    String[] lcArray = lcTemp.split("-");
                                    carBuf.append(strTemp + " ( ljcz BETWEEN ? AND ? )");
                                    strTemp = " or ";
                                    params.add(Integer.valueOf(lcArray[0]));
                                    params.add(Integer.valueOf(lcArray[1]));
                                }
                            }
                            carBuf.append(" ) ");
                            if (lsBuf.toString().indexOf(" OR ") > -1) {
                                carBuf.append(" AND (1 >2 ");
                                carBuf.append(lsBuf.toString());
                                carBuf.append(" )");
                            }
                        }
                    }
                    //工单平均产值
                    Object avgCzObj = paramsMap.get("avgCz");
                    if (null != avgCzObj) {
                        List<String> avgCzLs = (List<String>) avgCzObj;
                        if (avgCzLs.size() > 0) {
                            StringBuffer lsBuf = new StringBuffer();
                            for (String lcTemp : avgCzLs) {
                                if (lcTemp.indexOf("-") > 0) {
                                    String[] lcArray = lcTemp.split("-");
                                    lsBuf.append(" OR TEMP.avgCz BETWEEN ? AND ? ");
                                    params.add(lcArray[0]);
                                    params.add(lcArray[1]);
                                }
                            }
                            if (lsBuf.toString().length() > 0) {
                                temp2Buf.append(" AND (1 >2 ");
                                temp2Buf.append(lsBuf.toString());
                                temp2Buf.append(" )");
                            }
                        }
                    }
                    //进厂次数
                    Object jcObj = paramsMap.get("jc");
                    if (null != jcObj) {
                        List jcLs = (List) jcObj;
                        if (jcLs.size() > 0) {

                            temp2Buf.append(" AND(  ");
                            StringBuffer tempAge = new StringBuffer(" TEMP.jccs IN ( ");
                            StringBuffer temAgeG = new StringBuffer(" TEMP.jccs >= ? ");
                            Object lsYear = null;
                            for (Object obj : jcLs) {
                                if (Integer.valueOf(String.valueOf(obj)) > 8) {
                                    lsYear = obj;
                                } else {
                                    tempAge.append("?,");
                                    params.add(obj);
                                }
                            }
                            tempAge.append(" '')  ");

                            if (null != lsYear) {
                                tempAge.append(" OR (").append(temAgeG).append(") ");
                                params.add(lsYear);
                            }
                            temp2Buf.append(tempAge);
                            temp2Buf.append(" )");
                        }
                    }
                    carBuf.append(temp2Buf.toString());
                }
            }
        }
        if (data != null) {
            carBuf.append(" and temp.car_id in ( ");
            for (int i = 0; i < data.size(); i++) {
                String carId = String.valueOf(data.get(i).get("carId"));
                carBuf.append(" ? ");
                params.add(carId);
                if (i < (data.size() - 1)) {
                    carBuf.append(",");
                }
            }
            carBuf.append(" ) ");
            start = "0";
            limit = String.valueOf(data.size());
        }
        res = this.carDao.page(carBuf.toString(), params.toArray(), start, limit);
        return res;
    }


    /**
     * 查询用户 分配的车辆信息
     *
     * @param uid
     * @return
     * @throws Exception
     */
    private Map<String, Object> refCar(String uid) throws Exception {
        String sql = "select  * from t_crm_car_ref where u_id = ? and status = '1' ";
        Map info = this.carDao.info(sql, uid);
        return info;
    }

    @Override
    public List<Map<String, Object>> excelZH(Map params, String uid) throws Exception {


        Map temp = new HashMap();
        temp.put("total", 0);
        temp.put("dataList", new ArrayList());
        //UID
        //根据用户取公司 ID，默认仅能查询本公司的车辆信息
        String companyId = TokenUtil.getCompanyId(uid);
        Long curTime = new Date().getTime();
        //组合查询
        StringBuffer temp2Buf = new StringBuffer();

        StringBuffer carBuf = new StringBuffer();
        List list = new ArrayList();
        carBuf.append("SELECT * FROM (SELECT tc.plate_num, car_id, ");
        carBuf.append("SUM(IFNULL(real_amount, 0)) ljcz, ");
        carBuf.append("count( DISTINCT FROM_UNIXTIME(balance_date / 1000, '%Y%m%d') ) AS jccs,  ");
        carBuf.append("round( sum(IFNULL(real_amount, 0)) / count( DISTINCT FROM_UNIXTIME(balance_date / 1000, '%y%m%d') ),2) avgCz, ");
        carBuf.append("SUM(CASE WHEN `balance_date` BETWEEN ? AND ? THEN real_amount ELSE 0 END) AS mCz,  ");
        carBuf.append("tc.first_enter_date as firsEnterDate, ");
        carBuf.append("MAX( CASE WHEN real_amount > '0.00' THEN balance_date ELSE 0 END ) AS lastEnterDate,  ");
        carBuf.append("tc.vin,  ");
        carBuf.append("tc.from_company,  ");
        carBuf.append("tc.owner_city, ");
        carBuf.append("tc.owner_name,  ");
        carBuf.append("tc.series, ");
        carBuf.append("tc.car_age, ");
        carBuf.append("tc.run_mile,tc.contact_phone  ");
        carBuf.append("FROM t_crm_car_workorder t1, t_crm_car tc ");
        carBuf.append("WHERE car_id = tc.id AND t1.real_amount > '0.00'  AND tc.company_id = ? ");
        Calendar cl = Calendar.getInstance();
        cl.setTime(new Date());
        cl.add(Calendar.MONTH, -1);
        list.add(cl.getTime().getTime());
        list.add(curTime);
        list.add(companyId);
        /* 新规则，按车驾号分配车辆 */
//        Constant.VIN_TO_SQL(carBuf, uid, "tc");
        if (companyId.equals(TokenUtil.getCompanyId(uid))) {
            confService.crmCarConfToSql(uid, carBuf, "tc", companyId);
        }
        Map res = null;


        //判断是否本店购车
        Object fromCompany = params.get("fromCompany");
        if (null != fromCompany && !fromCompany.toString().equals("")) {
            //fromcompany 1 查询是本店购车
            //            0 查询不是本店购车
            if (fromCompany.equals(String.valueOf(FROM_COMPANY_TYPE.YES.ordinal()))) {
                carBuf.append(" and tc.from_company = '是' ");
            } else {
                carBuf.append(" and tc.from_company <> '是' ");
            }
        }
        //        // 查询其分配的车辆
//        Map info = refCar(uid);
//        //未分配车辆
//        if (null == info || info.size() == 0) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//
//        //车辆分配类型
//        String refType = String.valueOf(info.get("type"));
//        if (StringUtils.isEmpty(refType) || "null".equals(refType.toLowerCase())) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//        //车辆分配值，车属号 或车尾数，vin时 另行处理
//        String refValue = String.valueOf(info.get("value"));
//        refValue = refValue.replaceAll(",", "");
//        //根据类型取车辆
//
//        switch (refType) {
//            case Constant.CAR_WH:
//                carBuf.append(" AND  tc.plate_num REGEXP ? ");
//                params.add("[" + refValue + "]$");
//                break;
//            case Constant.CAR_WS:
//                //先不写
//                carBuf.append(" AND tc.plate_num REGEXP ? ");
//                params.add(".*[" + refValue + "][A-Za-z]*$");
//                //根据尾数查询
//                break;
//            case Constant.CAR_ALL:
//                //全部车辆
//                break;
//            default:
//                throw new Exception("车辆分配错误，请联系管理员！");
//        }
        //车系参数
        Object seriesObj = params.get("series");
        if (null != seriesObj) {
            List seriesLs = (List) seriesObj;
            if (seriesLs.size() > 0) {
                carBuf.append(" AND tc.series in ( ");
                for (Object obj : seriesLs) {
                    carBuf.append(" ? ,");
                    list.add(obj);
                }
                carBuf.append(" '' )");
            }
        }
        //车龄参数
        Object ageObj = params.get("age");
        if (null != ageObj) {
            List ageLs = (List) ageObj;
            if (ageLs.size() > 0) {
                carBuf.append(" AND(  ");
                StringBuffer tempAge = new StringBuffer(" tc.car_age IN ( ");
                StringBuffer temAgeG = new StringBuffer(" tc.car_age >= ? ");
                Object lsYear = null;
                for (Object obj : ageLs) {
                    if (Integer.valueOf(String.valueOf(obj)) > 8) {
                        lsYear = obj;
                    } else {
                        tempAge.append("?,");
                        list.add(Integer.valueOf((String) obj));
                    }
                }
                tempAge.append(" '')  ");

                if (null != lsYear) {
                    tempAge.append(" OR (").append(temAgeG).append(") ");
                    list.add(Integer.valueOf((String) lsYear));
                }
                carBuf.append(tempAge);
                carBuf.append(" )");
            }
        }
        //里程
        Object lcObj = params.get("lc");
        if (null != lcObj) {
            List<String> lcLs = (List<String>) lcObj;
            if (lcLs.size() > 0) {

                StringBuffer lsBuf = new StringBuffer();
                for (String lcTemp : lcLs) {
                    if (lcTemp.indexOf("-") > 0) {
                        String[] lcArray = lcTemp.split("-");
                        lsBuf.append(" OR (tc.run_mile BETWEEN ? AND ? ) ");
                        list.add(Integer.valueOf(lcArray[0]));
                        list.add(Integer.valueOf(lcArray[1]));
                    }
                }
                if (lsBuf.toString().length() > 0) {
                    carBuf.append(" AND (1 >2 ");
                    carBuf.append(lsBuf.toString());
                    carBuf.append(" )");
                }

            }
        }
        //行政区域
        if (params.get("area") != null) {
            Object areaObj = params.get("area");
            String areaStr = areaObj.toString().replace(",", "|").replace("[", "").replace(" ", "").replace("]", "").replace("\"", "");
            if (areaStr.length() > 0) {
                carBuf.append(" AND tc.owner_city regexp ?");
                list.add(areaStr);
            }
        }
        // 工单阶段
        Object balanceObj = params.get("balanceDate");
        if (null != balanceObj) {
            Map balMap = (Map) balanceObj;
            if (balMap.size() > 0) {
                String startT = String.valueOf(balMap.get("start"));
                String endT = String.valueOf(balMap.get("end"));
                Long sTL = -1L;
                Long eTL = -1L;
                if (!"null".equals(startT) && startT != null && !"".equals(startT)) {
                    sTL = DateUtils.parseTime(startT + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
                }
                if (!"null".equals(endT) && endT != null && !"".equals(endT)) {
                    eTL = DateUtils.parseTime(endT + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
                }
                // 拼接 SQL
                if (sTL > 0 || eTL > 0) {
                    carBuf.append(" AND ( 1 = 1 ");
                    if (sTL > 0) {
                        carBuf.append(" AND t1.balance_date >= ? ");
                        list.add(sTL);
                    }
                    if (eTL > 0) {
                        carBuf.append(" AND t1.balance_date <= ? ");
                        list.add(eTL);
                    }
                    carBuf.append(" )");
                }
            }
        }
        //工单类别
        Object workTypeObj = params.get("workType");
        if (workTypeObj != null) {
            String workType = (String) workTypeObj;
            if (workType.length() > 0) {
                carBuf.append(" AND t1.type = ? ");
                list.add(workType);
            }
        }
        // 维修类别
        Object wxType = params.get("wxType");
        if (null != wxType) {
            List wxLs = (List) wxType;
            if (wxLs.size() > 0) {
                carBuf.append(" AND t1.wx_type in ( ");
                for (Object typeTemp : wxLs) {
                    carBuf.append("?,");
                    if (typeTemp.equals("维修业务")) {
                        typeTemp = "维修";
                    }
                    list.add(typeTemp);
                }
                carBuf.append(" '' )");

            }
        }
        //质保状态
        String zbStatus = String.valueOf(params.get("zbStatus"));
        if (!"null".equals(zbStatus) && zbStatus.length() > 0) {
            carBuf.append(" AND EXISTS (SELECT 1 from t_crm_car_exsurance  ex2 where ex2.user_carid  = tc.id ");

            switch (zbStatus) {
                case "0":
                    //在质保-里程 和进间都没进入
                    carBuf.append("  and ex2.mile_status = 0 and t1.balance_date < ex2.restart_insurance ");
                    break;
                case "1":
                    // 在延保 ：1 里程没进入时间进入，里程时入时间没进入，时间里程都进入
                    carBuf.append(" and ex2.mile_status < 2 and ( t1.balance_date  BETWEEN  ex2.restart_insurance and ex2.restop_insurance ) ");
                    break;
                case "2":
                    //里程超出 或时间超出
                    carBuf.append(" and (ex2.mile_status = 2 or t1.balance_date  > ex2.restop_insurance ) ");
                    break;
                default:
                    break;
            }
            //延保类别
            Object ybObj = params.get("ybType");
            if (null != ybObj) {
                List<String> ybLs = (List) ybObj;
                if (ybLs.size() > 0) {
                    carBuf.append(" AND ex2.exsurance_company_id IN( ");
                    for (String tempYb : ybLs) {
                        carBuf.append("?,");
                        list.add(tempYb);
                    }
                    carBuf.append(" '' ) ");
                    // carBuf.append(" )");
                }
            }
            carBuf.append(" )");
        }
        //保险类别
        /***
         * 暂时这样
         * ****/
        Object bxObj = params.get("bx");
        if (null != bxObj) {
            List<String> bxList = (List<String>) bxObj;
            if (bxList.size() > 0) {
                carBuf.append("AND EXISTS( ");
                carBuf.append(" SELECT 1 FROM t_crm_car_insurance ix WHERE ix.user_carid = tc.id  and  (t1.balance_date  BETWEEN ix.contractstartinsurance  and ix.contractstopinsurance) ");
                StringBuffer bxBuf = new StringBuffer();
                for (String bxTemp : bxList) {
                    switch (bxTemp) {
                        case "0":
                            bxBuf.append(" OR ix.type = '1' ");
                            break;
                        case "1":
                            bxBuf.append(" OR ix.type in ('2','3','4','5' )");
                            break;
                        default:
                            break;
                    }
                }
                if (bxBuf.toString().length() > 0) {
                    carBuf.append("AND ( 1 > 2 ");
                    carBuf.append(bxBuf.toString());
                    carBuf.append(" )");
                }
                carBuf.append(" )");
            }
        }
        //保险项目
        Object bxItemHhObj = params.get("bxItem");
        if (null != bxItemHhObj) {
            List<String> bxItemList = (List<String>) bxItemHhObj;
            if (bxItemList.size() > 0) {
                carBuf.append("AND EXISTS( ");
                carBuf.append(" SELECT 1 FROM t_crm_car_insurance ix WHERE ix.user_carid = c.id   ");
                carBuf.append(" SELECT 1 FROM t_crm_car_insurance ix WHERE ix.user_carid = tc.id  and  (t1.balance_date  BETWEEN ix.contractstartinsurance  and ix.contractstopinsurance)  ");
                StringBuffer bxBuf = new StringBuffer();
                for (String bxTemp : bxItemList) {
                    switch (bxTemp) {
                        case "0":
                            carBuf.append(" AND ix.boli = '1' ");
                            break;
                        case "1":
                            carBuf.append(" AND ix.huahen ='1' ");
                            break;
                    }
                }
                if (bxBuf.toString().length() > 0) {
                    carBuf.append("AND ( 1 =1 ");
                    carBuf.append(bxBuf.toString());
                    carBuf.append(" )");
                }

                carBuf.append(" ) ");
            }
        }
        carBuf.append("GROUP BY car_id ) AS TEMP WHERE 1=1 ");
        if (null != balanceObj) {
            Map balMap = (Map) balanceObj;
            if (balMap.size() > 0) {
                String startT = String.valueOf(balMap.get("start"));
                String endT = String.valueOf(balMap.get("end"));
                if (!"".equals(startT) && !"".equals(endT)) {
                    // 工单累计产值
                    Object ljCzObj = params.get("ljCz");
                    if (null != ljCzObj) {
                        List<String> ljCzLs = (List<String>) ljCzObj;
                        if (ljCzLs.size() > 0) {
                            StringBuffer lsBuf = new StringBuffer();
                            String strTemp = " and ( ";
                            for (String lcTemp : ljCzLs) {
                                if (lcTemp.indexOf("-") > 0) {
                                    String[] lcArray = lcTemp.split("-");
                                    carBuf.append(strTemp + " ( ljcz BETWEEN ? AND ? )");
                                    strTemp = " or ";
                                    list.add(Integer.valueOf(lcArray[0]));
                                    list.add(Integer.valueOf(lcArray[1]));
                                }
                            }
                            carBuf.append(" ) ");
                            if (lsBuf.toString().indexOf(" OR ") > -1) {
                                carBuf.append(" AND (1 >2 ");
                                carBuf.append(lsBuf.toString());
                                carBuf.append(" )");
                            }
                        }
                    }
                    //工单平均产值
                    Object avgCzObj = params.get("avgCz");
                    if (null != avgCzObj) {
                        List<String> avgCzLs = (List<String>) avgCzObj;
                        if (avgCzLs.size() > 0) {
                            StringBuffer lsBuf = new StringBuffer();
                            for (String lcTemp : avgCzLs) {
                                if (lcTemp.indexOf("-") > 0) {
                                    String[] lcArray = lcTemp.split("-");
                                    lsBuf.append(" OR TEMP.avgCz BETWEEN ? AND ? ");
                                    list.add(lcArray[0]);
                                    list.add(lcArray[1]);
                                }
                            }
                            if (lsBuf.toString().length() > 0) {
                                temp2Buf.append(" AND (1 >2 ");
                                temp2Buf.append(lsBuf.toString());
                                temp2Buf.append(" )");
                            }
                        }
                    }
                    //进厂次数
                    Object jcObj = params.get("jc");
                    if (null != jcObj) {
                        List jcLs = (List) jcObj;
                        if (jcLs.size() > 0) {

                            temp2Buf.append(" AND(  ");
                            StringBuffer tempAge = new StringBuffer(" TEMP.jccs IN ( ");
                            StringBuffer temAgeG = new StringBuffer(" TEMP.jccs >= ? ");
                            Object lsYear = null;
                            for (Object obj : jcLs) {
                                if (Integer.valueOf(String.valueOf(obj)) > 8) {
                                    lsYear = obj;
                                } else {
                                    tempAge.append("?,");
                                    list.add(obj);
                                }
                            }
                            tempAge.append(" '')  ");

                            if (null != lsYear) {
                                tempAge.append(" OR (").append(temAgeG).append(") ");
                                list.add(lsYear);
                            }
                            temp2Buf.append(tempAge);
                            temp2Buf.append(" )");
                        }
                    }
                    carBuf.append(temp2Buf.toString());
                }
            }
        }
        System.out.println(list);
        return this.carDao.list(carBuf.toString(), list.toArray());
    }

    @Override
    public XSSFWorkbook createExcel(List<Map<String, Object>> lists, String uid) {
        String[] excelHeaderRecordout = {"车牌号", "车型", "车主", "车龄", "地区", "月产值", "累计产值", "平均产值", "进厂", "首次进厂日期", "最后进厂日期", "VIN", "本店购车", "里程"};

        XSSFWorkbook wb = new XSSFWorkbook();

        XSSFSheet sheet = wb.createSheet("组合查询车辆详情");


        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
            sheet.setDefaultRowHeightInPoints(24);
            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < lists.size(); i++) {
            Map map = (Map) lists.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(String.valueOf(map.get("plateNum")));
            row.createCell(1).setCellValue(String.valueOf(map.get("series")));
            row.createCell(2).setCellValue(String.valueOf(map.get("ownerName")));
            row.createCell(3).setCellValue(String.valueOf(map.get("carAge")));
            row.createCell(4).setCellValue(String.valueOf(map.get("ownerCity")));
            row.createCell(5).setCellValue(String.valueOf(map.get("mCz")));
            row.createCell(6).setCellValue(String.valueOf(map.get("ljcz")));
            row.createCell(7).setCellValue(String.valueOf(map.get("avgCz")));
            row.createCell(8).setCellValue(String.valueOf(map.get("jccs")));
            row.createCell(9).setCellValue(String.valueOf(map.get("firsEnterDate")));
            row.createCell(10).setCellValue(String.valueOf(map.get("lastEnterDate")));
            row.createCell(11).setCellValue(String.valueOf(map.get("vin")));
            row.createCell(12).setCellValue(String.valueOf(map.get("fromCompany")).equals("是") ? "是" : "否");
            row.createCell(13).setCellValue(String.valueOf(map.get("runMile")));
        }
        return wb;
    }
}
